/**
 * 更新stats_cache表的脚本
 * 从现有的缓存服务或直接计算统计数据并存入数据库
 * 更新策略：6个月以上的统计数据缓存时间为1天
 */

const fs = require('fs');
const path = require('path');
const { DateTime } = require('luxon');
const mysql = require('mysql2/promise');

// 创建数据库连接池
const pool = mysql.createPool({
  host: process.env.DB_HOST || '127.0.0.1',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'BinanceRoot2023',
  database: process.env.DB_NAME || 'binance_klines',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 支持的交易对
const SYMBOLS = ['BTCUSDT', 'ETHUSDT'];

// 支持的时间间隔
const INTERVALS = ['5m', '10m', '15m', '30m', '1h', '1d'];

// 支持的时间范围
const TIME_RANGES = {
  '1m': { months: 1 },
  '3m': { months: 3 },
  '6m': { months: 6 },
  '1y': { years: 1 },
  '2y': { years: 2 },
  '3y': { years: 3 },
  '4y': { years: 4 },
  '5y': { years: 5 },
  '6y': { years: 6 },
  '7y': { years: 7 }
};

// API统计目录
const PUBLIC_API_DIR = path.join(__dirname, '../public/api/stats');

// 缓存文件目录
const CACHE_DIR = path.join(__dirname, '../cache/stats');

/**
 * 从文件系统缓存获取统计数据
 * @param {string} symbol 交易对
 * @param {string} interval 时间间隔
 * @param {string} range 时间范围
 * @returns {object|null} 统计数据
 */
function getStatsFromFileCache(symbol, interval, range) {
  try {
    const jsonFilePath = path.join(PUBLIC_API_DIR, `${symbol.toLowerCase()}_${interval}_${range}.json`);
    
    if (!fs.existsSync(jsonFilePath)) {
      console.log(`缓存文件不存在: ${jsonFilePath}`);
      return null;
    }
    
    // 读取缓存文件
    const cacheContent = fs.readFileSync(jsonFilePath, 'utf8');
    const cache = JSON.parse(cacheContent);
    
    if (!cache.data) {
      console.log(`缓存文件格式错误: ${jsonFilePath}`);
      return null;
    }
    
    return cache.data;
  } catch (error) {
    console.error(`从文件缓存获取数据失败: ${error.message}`);
    return null;
  }
}

/**
 * 计算统计数据
 * @param {string} symbol 交易对
 * @param {string} interval 时间间隔
 * @param {number} startTime 开始时间戳
 * @param {number} endTime 结束时间戳
 * @returns {object} 统计数据
 */
async function calculateStats(symbol, interval, startTime, endTime) {
  const tableName = `kline_${interval}_enhanced`;
  
  // 查询总K线数量
  const [totalResult] = await pool.query(
    `SELECT COUNT(*) as total FROM ${tableName} 
     WHERE symbol = ? AND open_time BETWEEN ? AND ?`,
    [symbol, startTime, endTime]
  );
  
  // 查询上涨K线数量
  const [upResult] = await pool.query(
    `SELECT COUNT(*) as up FROM ${tableName} 
     WHERE symbol = ? AND open_time BETWEEN ? AND ? 
     AND close_price > open_price`,
    [symbol, startTime, endTime]
  );
  
  // 查询下跌K线数量
  const [downResult] = await pool.query(
    `SELECT COUNT(*) as down FROM ${tableName} 
     WHERE symbol = ? AND open_time BETWEEN ? AND ? 
     AND close_price < open_price`,
    [symbol, startTime, endTime]
  );
  
  // 查询平盘K线数量
  const [flatResult] = await pool.query(
    `SELECT COUNT(*) as flat FROM ${tableName} 
     WHERE symbol = ? AND open_time BETWEEN ? AND ? 
     AND close_price = open_price`,
    [symbol, startTime, endTime]
  );

  // 获取总K线数量
  const totalKlines = totalResult[0].total;
  const upKlines = upResult[0].up;
  const downKlines = downResult[0].down;
  const flatKlines = flatResult[0].flat;

  // 直接使用SQL查询获取连续上涨数据
  const [upStreakResults] = await pool.query(
    `SELECT count, COUNT(*) as occurrences 
     FROM (
       SELECT 
         @streak := IF(@prev_symbol = BINARY symbol AND @prev_close < close_price, 
                      @streak + 1, 
                      IF(@prev_symbol = BINARY symbol AND @prev_close >= close_price, 0, 1)
                    ) AS count,
         @prev_symbol := symbol, 
         @prev_close := close_price
       FROM ${tableName}, 
         (SELECT @streak := 0, @prev_symbol := '', @prev_close := 0) AS vars
       WHERE symbol = ? 
         AND open_time BETWEEN ? AND ?
       ORDER BY symbol, open_time
     ) AS streaks 
     WHERE count >= 2
     GROUP BY count`,
    [symbol, startTime, endTime]
  );

  // 直接使用SQL查询获取连续下跌数据
  const [downStreakResults] = await pool.query(
    `SELECT count, COUNT(*) as occurrences 
     FROM (
       SELECT 
         @streak := IF(@prev_symbol = BINARY symbol AND @prev_close > close_price, 
                      @streak + 1, 
                      IF(@prev_symbol = BINARY symbol AND @prev_close <= close_price, 0, 1)
                    ) AS count,
         @prev_symbol := symbol, 
         @prev_close := close_price
       FROM ${tableName}, 
         (SELECT @streak := 0, @prev_symbol := '', @prev_close := 0) AS vars
       WHERE symbol = ? 
         AND open_time BETWEEN ? AND ?
       ORDER BY symbol, open_time
     ) AS streaks 
     WHERE count >= 2
     GROUP BY count`,
    [symbol, startTime, endTime]
  );

  // 将结果转换为对象格式
  const consecutiveUp = {};
  const consecutiveDown = {};

  upStreakResults.forEach(row => {
    consecutiveUp[row.count] = row.occurrences;
  });

  downStreakResults.forEach(row => {
    consecutiveDown[row.count] = row.occurrences;
  });

  return {
    total: totalKlines,
    up: upKlines,
    down: downKlines,
    flat: flatKlines,
    consecutive: {
      up: consecutiveUp,
      down: consecutiveDown
    },
    timeRange: {
      start: new Date(startTime).toISOString(),
      end: new Date(endTime).toISOString()
    }
  };
}

/**
 * 更新数据库缓存
 * @param {string} symbol 交易对
 * @param {string} interval 时间间隔
 * @param {string} range 时间范围
 * @param {object} data 统计数据
 */
async function updateDatabaseCache(symbol, interval, range, data) {
  try {
    // 调用存储过程更新缓存
    await pool.query(
      'CALL update_stats_cache(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [
        symbol,
        interval,
        range,
        JSON.stringify(data),
        data.total,
        data.up,
        data.down,
        data.flat,
        JSON.stringify(data.consecutive.up),
        JSON.stringify(data.consecutive.down)
      ]
    );
    
    console.log(`成功更新数据库缓存: ${symbol} ${interval} ${range}`);
  } catch (error) {
    console.error(`更新数据库缓存失败: ${error.message}`);
    throw error;
  }
}

/**
 * 主函数：更新所有缓存
 */
async function updateAllCache() {
  console.log('开始更新stats_cache表...');
  console.log(`当前时间: ${new Date().toISOString()}`);
  console.log('注意：长时间范围的统计数据(6个月、1年、2年)缓存时间已设置为1天');
  
  try {
    // 检查数据库连接
    await pool.query('SELECT 1');
    console.log('数据库连接成功');
    
    // 对每个交易对、时间间隔和时间范围执行更新
    for (const symbol of SYMBOLS) {
      for (const interval of INTERVALS) {
        for (const [range, duration] of Object.entries(TIME_RANGES)) {
          try {
            console.log(`处理 ${symbol} ${interval} ${range}...`);
            
            // 尝试从文件缓存获取数据
            let stats = getStatsFromFileCache(symbol, interval, range);
            
            // 如果文件缓存不存在，则计算统计数据
            if (!stats) {
              console.log(`文件缓存不存在，计算统计数据: ${symbol} ${interval} ${range}`);
              
              // 计算时间范围
              const now = DateTime.now().setZone('Asia/Hong_Kong');
              const startTime = now.minus(duration).toMillis();
              const endTime = now.toMillis();
              
              stats = await calculateStats(symbol, interval, startTime, endTime);
            }
            
            // 更新数据库缓存
            await updateDatabaseCache(symbol, interval, range, stats);
          } catch (error) {
            console.error(`处理 ${symbol} ${interval} ${range} 失败: ${error.message}`);
            // 继续处理下一个组合
          }
        }
      }
    }
    
    // 清理30天以上的过期缓存
    await pool.query('CALL clean_expired_cache(?)', [30]);
    console.log('已清理30天以上的过期缓存');
    
    console.log('所有缓存更新完成');
  } catch (error) {
    console.error(`更新缓存失败: ${error.message}`);
    process.exit(1);
  } finally {
    // 关闭数据库连接池
    await pool.end();
  }
}

/**
 * 清除所有缓存文件
 */
function clearAllCache() {
  console.log('开始清除统计数据缓存文件...');
  console.log(`当前时间: ${new Date().toISOString()}`);
  
  if (!fs.existsSync(CACHE_DIR)) {
    console.log(`缓存目录不存在: ${CACHE_DIR}`);
    fs.mkdirSync(CACHE_DIR, { recursive: true });
    console.log('已创建缓存目录');
    return;
  }
  
  try {
    // 读取缓存目录中的所有文件
    const files = fs.readdirSync(CACHE_DIR);
    
    if (files.length === 0) {
      console.log('缓存目录为空，无需清除');
      return;
    }
    
    // 遍历所有文件并删除
    let deletedCount = 0;
    for (const file of files) {
      if (file.endsWith('.json')) {
        fs.unlinkSync(path.join(CACHE_DIR, file));
        deletedCount++;
      }
    }
    
    console.log(`成功清除 ${deletedCount} 个缓存文件`);
  } catch (error) {
    console.error(`清除缓存文件失败: ${error.message}`);
    process.exit(1);
  }
}

// 如果直接运行脚本，执行更新操作
if (require.main === module) {
  updateAllCache();
}

// 建议的crontab设置
console.log("建议的crontab设置: 0 1 * * * cd /var/www/binance-analysis && node scripts/update_stats_cache.js");

module.exports = {
  updateAllCache,
  clearAllCache
}; 